package ig.util;

import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.SQLException;
import java.sql.Timestamp;




/**

 *

 * @author eliecer

 */

public class Pruebas {

    

    /** Creates a new instance of Pruebas */    

   public Pruebas() {

        

    }

   public static void main(String[] arg){
        
        String cadenaSTR = "";
        
        Pruebas p=new Pruebas();     
        //String connectURL="jdbc:odbc:METRICADW";
//        String connectURL="jdbc:jtds:sqlserver://192.168.0.28:1433/Metricadb";
        String connectURL="jdbc:odbc:localServer";
        String uName="sa";
        String pWord="";

        String driverName="sun.jdbc.odbc.JdbcOdbcDriver"; 
//        String driverName="net.sourceforge.jtds.jdbc.Driver";
        String variableFiltro="";        
        Connection con=p.getConnections(connectURL,uName,pWord,driverName);
        System.out.println("ok");
//   Connection connectionreport, String usuario, String pwd, Timestamp fechaD,Timestamp fechaC,Double Valor1,Double Valor2, Double codorig, Double codportaf){
       Timestamp f1 = new java.sql.Timestamp(105,11,31,0,0,0,0);
       Timestamp f2 = new java.sql.Timestamp(106,2,31,0,0,0,0);
//       cadenaSTR ="SELECT SUM(AA.VER) AS VER, AA.FACTORRIESGO, FR.POSICION AS POSICION "+
//"FROM  "+
//"(SELECT VER AS VER, FACTORRIESGO AS FACTORRIESGO  "+
//"FROM METRICADW..VARCONTRATOSTASA VCT INNER JOIN  "+
//"METRICADW..CONTRATOS C ON VCT.IDCONTRATO = C.ID INNER JOIN  "+
//"METRICADW..PRODUCTOS P ON C.IDPRODUCTO = P.ID  "+
//"INNER JOIN METRICADW..UNIDADESNEGOCIOS U ON C.IDUNIDADNEGOCIO = U.ID  "+
//"WHERE U.IDEMPRESA = 1  "+
//"AND  VCT.IDESCENARIO = 6 "+
//"AND  C.IDESCENARIO = 6 "+
//"AND  P.IDESCENARIO = 6 "+
//"AND  U.IDESCENARIO = 6 "+
//"UNION ALL  "+
//"SELECT VER AS VER, FACTORRIESGO AS FACTORRIESGO  "+
//"FROM METRICADW..VARCONTRATOSPRECIO VCP INNER JOIN  "+
//"METRICADW..CONTRATOS C ON VCP.IDCONTRATO = C.ID INNER JOIN  "+
//"METRICADW..PRODUCTOS P ON C.IDPRODUCTO = P.ID  "+
//"INNER JOIN METRICADW..UNIDADESNEGOCIOS U ON C.IDUNIDADNEGOCIO = U.ID  "+
//"WHERE U.IDEMPRESA = 1 "+
//"AND  VCP.IDESCENARIO = 6 "+
//"AND  C.IDESCENARIO = 6 "+
//"AND  P.IDESCENARIO = 6 "+
//"AND  U.IDESCENARIO = 6) "+
//"AS AA  "+
//"INNER JOIN METRICADW..FACTORESRIESGO FR ON AA.FACTORRIESGO = FR.NOMBRE  "+
//"GROUP BY AA.FACTORRIESGO, FR.POSICION  "+
//"UNION  "+
//"SELECT SUM(PE.VALORNOMINAL*CEM.CAMBIOESPERADO*VM.VALOR) AS VER,A.FACTORRIESGO,A.POSICION  "+
//"FROM METRICADW..POSICIONESESPECIES PE INNER JOIN  "+
//"METRICADW..CONTRATOS C ON C.ID=PE.ID INNER JOIN  "+
//"METRICADW..UNIDADESNEGOCIOS U ON U.ID=C.IDUNIDADNEGOCIO INNER JOIN  "+
//"METRICADW..ESPECIES E ON PE.IDESPECIE=E.ID INNER JOIN  "+
//"(SELECT F.ID AS POSICION, M.ID AS IDMONEDA,F.NOMBRE AS FACTORRIESGO  "+
//"FROM METRICADW..ASOCIACIONFACTORES A INNER JOIN  "+
//"METRICADW..FACTORESRIESGOPERSONALES FR ON A.IDFACTORRIESGOPERSONAL = FR.ID INNER JOIN  "+
//"METRICADW..FACTORESRIESGO F ON FR.IDFACTORMATRIZ = F.ID INNER JOIN  "+
//"METRICADW..MONEDAS M ON A.ENTIDAD = M.NOMBRE COLLATE Modern_Spanish_CI_AS  "+
//"INNER JOIN METRICADW..ESCENARIOS ES ON ES.IDMODELO = A.IDMODELO "+
//"WHERE ES.ID  = 6) A  "+
//"ON E.IDMONEDA=A.IDMONEDA  "+
//"AND E.IDMONEDA<>1  "+
//"INNER JOIN METRICADW..CAMBIOESPERADOMONEDAS CEM  "+ 
//"ON CEM.IDMONEDA=E.IDMONEDA  "+
//"INNER JOIN METRICADW..VALORESMONEDAS VM  "+
//"ON VM.IDMONEDA=E.IDMONEDA AND VM.FECHA=  "+
//"(SELECT DISTINCT FECHA FROM METRICADW..ESCENARIOS ESC WHERE ESC.ID = 3)  "+
//"WHERE PE.IDESCENARIO = 6 "+
//"AND C.IDESCENARIO = 6 "+
//"AND U.IDESCENARIO = 6 "+
//"AND E.IDESCENARIO = 6 "+
//"AND CEM.IDESCENARIO = 6 "+
//" AND VM.IDESCENARIO = 6 "+
//"GROUP BY FACTORRIESGO,POSICION  "+
//"ORDER BY POSICION";           
        cadenaSTR="SELECT  V.FECHACOR as FECHA, V.DIASVENC AS DIASVENC, V.VALTOTAL AS CAPITAL, 0 AS INTERESFIJO,0 AS INTERESVARIABLE,1 AS CONOCIDO FROM VENCFUT V INNER JOIN Portafol P ON V.CODPORTAF = P.CODPORTAF INNER JOIN PortGrup PG ON P.CODPORTAF = PG.CODPORTAF INNER JOIN GruPort G ON PG.CODGRUPO = G.CODGRUPO WHERE  (V.FECHAEVAL = '2006-01-31 00:00:00.0' AND (G.CODGRUPO = 8.0)) UNION SELECT   FECHATASA AS FECHA, DATEDIFF(DAY,'2006-01-31 00:00:00.0',FECHATASA) AS DIASVENC, -VALOR*1000 AS CAPITAL,0 AS INTERESFIJO,0 AS INTERESVARIABLE,1 AS CONOCIDO  FROM TASVAL WHERE (TIPOTASA = 'P.U.G.' OR TIPOTASA = 'PENSION') AND FECHATASA > '2006-01-31 00:00:00.0' ORDER BY DIASVENC  " ;
        System.out.println("Tir:"+TirOfFlujo.getTirEspeciePortafolio(con,"sa","",f1,f2,744095689.0,0.0,165.0,2.0));
//System.out.println("Tir:"+TirOfFlujo.getTir(con,"sa","",cadenaSTR,f1,f2));
//              System.out.println("Var Diversificado:"+VarDiversificado.getVarDiversificadoConsultaSql(con,"sa","",cadenaSTR));
     }

   private Connection getConnections(String connectURL, String uName, String pWord, String driverName){         
          System.out.println("driver name: "+driverName.trim());
         try{
             Class.forName(driverName).newInstance();
             return DriverManager.getConnection(connectURL,uName,pWord);
         }catch(ClassNotFoundException ex){
             System.out.println(ex);
         }catch(InstantiationException ex){
             System.out.println(ex);
         }catch(IllegalAccessException ex){
             System.out.println(ex);
         }catch(SQLException ex){
             System.out.println(ex);
             ex.printStackTrace();
         }
          return null;
    }
}
